package com.wawayaya.db.bookreader;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;
import android.text.TextUtils;

import com.wawayaya.bookreader.bean.IntDouble;
import com.wawayaya.bookreader.worddict.EnWordDictDescData;
import com.wawayaya.util.TLog;
import java.io.File;
import java.util.ArrayList;


/**
 * Created by wang_qian on 2015/11/9.
 */
public class DictionaryENDB {
    private static final int DATABASE_VERSION = 1;

    public SQLiteDatabase m_dbInternal = null;
    private static String m_sdictdbpath = null;

    private static final String TBNAME_LOCALDICTCACHE = "tb_endicttext";

    private static final String COLUMN_ID = "id";
    private static final String COLUMN_NETID = "netid";
    private static final String COLUMN_BOOKID = "bookid";
    private static final String COLUMN_SENTENCE = "sentence";
    private static final String COLUMN_WORDINDEX = "wordindex";
    private static final String COLUMN_CLICKWORD = "clickword";


    public static final String COLNAME_DICTINDEX = "dictindex";
    public static final String COLNAME_CIXINGINDEX = "cixingindex";
    public static final String COLNAME_TYPE = "type";
    public static final String COLNAME_TYPEVALUE = "typevalue";

    public static final String TYPENAME_BASEWORD = "baseword";
    public static final String TYPENAME_SOUNDURL = "soundurl";
    public static final String TYPENAME_DICTNAME = "dictname";
    public static final String TYPENAME_DICTTYPE = "dicttype";
    public static final String TYPENAME_YINBIAO = "yinbiao";
    public static final String TYPENAME_CIXING = "cixing";
    public static final String TYPENAME_FUSHU = "fushu";
    public static final String TYPENAME_JIESHI = "jieshi";
    public static final String TYPENAME_LIJU = "liju";
    public static final String TYPENAME_LIJUJIESHI = "lijujieshi";


    private static final String CREATE_SQL_LOCALDICTCACHE =
            "CREATE TABLE "+ TBNAME_LOCALDICTCACHE + "("+
                    COLUMN_ID		+	" integer PRIMARY KEY AUTOINCREMENT," +
                    COLUMN_NETID	+	" integer not null," +
                    COLUMN_BOOKID	+	" integer not null," +
                    COLUMN_SENTENCE +   " text not null," +
                    COLUMN_WORDINDEX +	" integer not null," +
                    COLUMN_CLICKWORD +  " text not null," +
                    COLNAME_DICTINDEX	+	" integer not null," +
                    COLNAME_CIXINGINDEX	+	" integer not null," +
                    COLNAME_TYPE + " text not null," +
                    COLNAME_TYPEVALUE + " text);";

    private static final String INSERT_SQL_LOCALDICTCACHE = "INSERT INTO "+
            TBNAME_LOCALDICTCACHE +" ("+
            COLUMN_ID + ","+
            COLUMN_NETID + ","+
            COLUMN_BOOKID + "," +
            COLUMN_SENTENCE + "," +
            COLUMN_WORDINDEX + "," +
            COLUMN_CLICKWORD + "," +
            COLNAME_DICTINDEX	+ "," +
            COLNAME_CIXINGINDEX + "," +
            COLNAME_TYPE + "," +
            COLNAME_TYPEVALUE +
            ") VALUES (NULL, %d, %d, \"%s\",%d, \"%s\",%d, %d,\"%s\", \"%s\");";

    /**
     * 初始化跳转的地址
     * @param context
     */
     public DictionaryENDB(Context context) {
        // androi4.2 中引入了多用户机制，普通用户无法访问根目录下的/data/data目录，因而无法创建数据库文件
        // androi4.4 WRITE_EXTERNAL_STORAGE只为设备上的主要外部存储授予写权限，应用程序无法将数据写入二级外部存储设备
        // androi4.4 sd卡下只能在 sdcard/data/package-name 这个目录下创建数据库文件
        if(null != m_sdictdbpath) {
            try{
                DatabaseHelper dbHelper = new DatabaseHelper(context, m_sdictdbpath);
                if(null != dbHelper) {
                    m_dbInternal = dbHelper.getWritableDatabase();
                }
            }catch(Exception e){
            }
        }
        if(null == m_dbInternal) {
            String sFilepath;
           /* try {
                sFilepath = utils.getireadckclocaldictionarypath(utils.getparentfolder(BookShelfActivity.m_sbookzipsavedpath), true);
                DatabaseHelper dbHelper = new DatabaseHelper(context, sFilepath);
                if(null != dbHelper) {
                    m_dbInternal = dbHelper.getWritableDatabase();
                }
                if(null != m_dbInternal){
                    m_sdictdbpath = sFilepath;
                }
            } catch(android.database.sqlite.SQLiteCantOpenDatabaseException soe){
                try{
                    sFilepath = Environment.getExternalStorageDirectory().getAbsolutePath() + "/Android/data/wawayaya.kids_iread/";
                    new File(sFilepath).mkdirs();
                    sFilepath += "enlcdd.d";
                    DatabaseHelper dbHelper = new DatabaseHelper(context, sFilepath);
                    if(null != dbHelper) {
                        m_dbInternal = dbHelper.getWritableDatabase();
                    }
                    if(null != m_dbInternal){
                        m_sdictdbpath = sFilepath;
                    }
                }catch(android.database.sqlite.SQLiteCantOpenDatabaseException soe2){
                    TLog.log("DictionaryENDB : dbBookShelfCoverImage : soe2 : " + soe2);
                }
            }
            catch (Exception e) {
                TLog.log("DictionaryENDB : dbBookShelfCoverImage : e : " + e);
            }
            if(null == m_dbInternal) {
                String spath = context.getFilesDir().getAbsolutePath();
                if(!TextUtils.isEmpty(spath)){
                    try{
                        sFilepath = utils.getireadckclocaldictionarypath(spath, true);
                        DatabaseHelper dbHelper = new DatabaseHelper(context, sFilepath);
                        if(null != dbHelper) {
                            m_dbInternal = dbHelper.getWritableDatabase();
                        }
                        m_sdictdbpath = sFilepath;
                    }catch(Exception e2){
                        TLog.log("DictionaryENDB : dbBookShelfCoverImage : e2 : " + e2);
                    }
                }
            }*/
        }
    }

    public void close() {
        m_sdictdbpath = null;
        if(null != m_dbInternal) {
            m_dbInternal.close();
            m_dbInternal = null;
        }
    }

    public IntDouble[] getWordDictRange(int bookid, String sentence, int wordindex, String clickword, boolean blur) {
        if(null == m_dbInternal || TextUtils.isEmpty(sentence) || wordindex < 0){
            return null;
        }

        int startId = -1;
        int endId = -1;
       // ArrayList<StringInt> alpinyins = new ArrayList<StringInt>();
        Cursor c = null;
        try{
            String ssel = COLUMN_BOOKID + "=\"" + bookid + "\" and " + COLUMN_SENTENCE + "=\"" + makedbreadablestring(sentence) + "\" and " +
                    COLUMN_WORDINDEX + "=\"" + wordindex + "\"";
            c = m_dbInternal.query(TBNAME_LOCALDICTCACHE,
                    new String[]{COLUMN_ID},
                    ssel,
                    null, null, null, COLUMN_ID);
            if(null == c || !c.moveToFirst()){
                return null;
            }
            startId = c.getInt(0);
            endId = startId;
            while(c.moveToNext()) {
                endId = c.getInt(0);
            }
            if(startId < 0 || endId < 0){
                return null;
            }
        }catch(Exception e0){
            TLog.log("DictionaryCNDB : getwordtextitemrange : e0 : " + e0);
        }finally{
            if(null != c) {
                c.close();
            }
            c = null;
        }

        try{

            String ssel = COLUMN_BOOKID + "=\"" + bookid + "\" and " + COLUMN_SENTENCE + "=\"" + makedbreadablestring(sentence) + "\" and " +
                    COLUMN_WORDINDEX + "=\"" + wordindex + "\" and " + COLUMN_ID + ">=" + startId;
            if (endId > 0) {
                ssel += " and " + COLUMN_ID + "<=" + endId;
            }


            ArrayList<IntDouble> alidu = new ArrayList<IntDouble>();
            c = m_dbInternal.query(TBNAME_LOCALDICTCACHE,
                    new String[]{COLUMN_ID, COLNAME_DICTINDEX},
                    ssel,
                    null, null, null, COLUMN_ID);
            if(null != c && c.moveToFirst()) {
                IntDouble idu = new IntDouble();
                int iitemidx = c.getInt(1);
                alidu.add(idu);
                idu.m_ival1 = c.getInt(0);
                while(c.moveToNext()) {
                    if(iitemidx == c.getInt(1)){
                        idu.m_ival2 = c.getInt(0);
                    }
                    else{
                        idu = new IntDouble();
                        iitemidx = c.getInt(1);
                        alidu.add(idu);
                        idu.m_ival1 = c.getInt(0);
                    }
                }
                int isize = alidu.size();
                if(isize <= 0) {
                    return null;
                }
                IntDouble[] idus = new IntDouble[isize];
                for(int i = 0; i < isize; i++) {
                    idus[i] = alidu.get(i);
                }
                alidu.clear();
                return idus;
            }
        }
        catch(Exception e){
            TLog.log("DictionaryCNDB : getwordtextitemrange : e : " + e);
        }
        finally{
            if(null != c) {
                c.close();
            }
        }
        return null;


    }


    public String getWordDictDuyinUrl(int dictid, int bookid, String sentence, int wordindex, int ihead, int itail){
        if(null == m_dbInternal || TextUtils.isEmpty(sentence)){
            return null;
        }
        Cursor c = null;
        try{
            String ssel = COLUMN_BOOKID + "=\"" + bookid + "\" and " + COLUMN_SENTENCE + "=\"" + makedbreadablestring(sentence) + "\" and " +
                    COLUMN_WORDINDEX + "=\"" + wordindex + "\" and " + COLNAME_TYPE + "=\"" + TYPENAME_SOUNDURL + "\" and " + COLNAME_DICTINDEX + "=\"" + dictid + "\"" ;
            ssel += " and " + COLUMN_ID + " >= " + ihead + " and " + COLUMN_ID + "<=" + itail;
            c = m_dbInternal.query(TBNAME_LOCALDICTCACHE,
                    new String[]{COLNAME_TYPEVALUE},
                    ssel,
                    null, null, null, COLUMN_ID);
            if(null != c && c.moveToFirst()) {
                return c.getString(0);
            }
        }
        finally{
            if(null != c) {
                c.close();
            }
        }
        return null;
    }

    public String getWordDictBaseWord(int dictid, int bookid, String sentence, int wordindex, int ihead, int itail){
        if(null == m_dbInternal || TextUtils.isEmpty(sentence)){
            return null;
        }
        Cursor c = null;
        try{
            String ssel = COLUMN_BOOKID + "=\"" + bookid + "\" and " + COLUMN_SENTENCE + "=\"" + makedbreadablestring(sentence) + "\" and " +
                    COLUMN_WORDINDEX + "=\"" + wordindex + "\" and " + COLNAME_TYPE + "=\"" + TYPENAME_BASEWORD + "\" and " + COLNAME_DICTINDEX + "=\"" + dictid + "\"" ;
            ssel += " and " + COLUMN_ID + " >= " + ihead + " and " + COLUMN_ID + "<=" + itail;
            c = m_dbInternal.query(TBNAME_LOCALDICTCACHE,
                    new String[]{COLNAME_TYPEVALUE},
                    ssel,
                    null, null, null, COLUMN_ID);
            if(null != c && c.moveToFirst()) {
                return c.getString(0);
            }
        }  catch(Exception e){
            TLog.log("DictionaryCNDB : getwordtextitemrange : e : " + e);
        }
        finally{
            if(null != c) {
                c.close();
            }
        }
        return null;
    }

    public String getWordDictName(int dictid, int bookid, String sentence, int wordindex, int ihead, int itail){
        if(null == m_dbInternal || TextUtils.isEmpty(sentence)){
            return null;
        }
        Cursor c = null;
        try{
            String ssel = COLUMN_BOOKID + "=\"" + bookid + "\" and " + COLUMN_SENTENCE + "=\"" + makedbreadablestring(sentence) + "\" and " +
                    COLUMN_WORDINDEX + "=\"" + wordindex + "\" and " + COLNAME_TYPE + "=\"" + TYPENAME_DICTNAME+ "\" and " + COLNAME_DICTINDEX + "=\"" + dictid + "\"" ;
            ssel += " and " + COLUMN_ID + " >= " + ihead + " and " + COLUMN_ID + "<=" + itail;
            c = m_dbInternal.query(TBNAME_LOCALDICTCACHE,
                    new String[]{COLNAME_TYPEVALUE},
                    ssel,
                    null, null, null, COLUMN_ID);
            if(null != c && c.moveToFirst()) {
                return c.getString(0);
            }
        }
        catch(Exception e){
            TLog.log("DictionaryCNDB : getwordtextitemrange : e : " + e);
        }
        finally{
            if(null != c) {
                c.close();
            }
        }
        return null;
    }

    public String getWordDictType(int dictid, int bookid, String sentence, int wordindex, int ihead, int itail){
        if(null == m_dbInternal || TextUtils.isEmpty(sentence)){
            return null;
        }
        Cursor c = null;
        try{
            String ssel = COLUMN_BOOKID + "=\"" + bookid + "\" and " + COLUMN_SENTENCE + "=\"" + makedbreadablestring(sentence) + "\" and " +
                    COLUMN_WORDINDEX + "=\"" + wordindex + "\" and " + COLNAME_TYPE + "=\"" + TYPENAME_DICTTYPE + "\" and " + COLNAME_DICTINDEX + "=\"" + dictid + "\"" ;
            ssel += " and " + COLUMN_ID + " >= " + ihead + " and " + COLUMN_ID + "<=" + itail;
            c = m_dbInternal.query(TBNAME_LOCALDICTCACHE,
                    new String[]{COLNAME_TYPEVALUE},
                    ssel,
                    null, null, null, COLUMN_ID);
            if(null != c && c.moveToFirst()) {
                return c.getString(0);
            }
        }
        catch(Exception e){
            TLog.log("DictionaryCNDB : getwordtextitemrange : e : " + e);
        }
        finally{
            if(null != c) {
                c.close();
            }
        }
        return null;
    }

    public String getWordDictYinBiao(int dictid, int bookid, String sentence, int wordindex, int ihead, int itail){
        if(null == m_dbInternal || TextUtils.isEmpty(sentence)){
            return null;
        }
        Cursor c = null;
        try{
            String ssel = COLUMN_BOOKID + "=\"" + bookid + "\" and " + COLUMN_SENTENCE + "=\"" + makedbreadablestring(sentence) + "\" and " +
                    COLUMN_WORDINDEX + "=\"" + wordindex + "\" and " + COLNAME_TYPE + "=\"" + TYPENAME_YINBIAO+ "\" and " + COLNAME_DICTINDEX + "=\"" + dictid + "\"" ;
            ssel += " and " + COLUMN_ID + " >= " + ihead + " and " + COLUMN_ID + "<=" + itail;
            c = m_dbInternal.query(TBNAME_LOCALDICTCACHE,
                    new String[]{COLNAME_TYPEVALUE},
                    ssel,
                    null, null, null, COLUMN_ID);
            if(null != c && c.moveToFirst()) {
                return makeUIreadablestring(c.getString(0));
            }
        }
        catch(Exception e){
            TLog.log("DictionaryCNDB : getwordtextitemrange : e : " + e);
        }
        finally{
            if(null != c) {
                c.close();
            }
        }
        return null;
    }


    public String getWordDictCixingDesc(int dictid, int cixing_id, int bookid, String sentence, int wordindex, int ihead, int itail){
        if(null == m_dbInternal || TextUtils.isEmpty(sentence)){
            return null;
        }
        Cursor c = null;
        try{
            String ssel = COLUMN_BOOKID + "=\"" + bookid + "\" and " + COLUMN_SENTENCE + "=\"" + makedbreadablestring(sentence) + "\" and " +
                    COLUMN_WORDINDEX + "=\"" + wordindex + "\" and " + COLNAME_TYPE + "=\"" + TYPENAME_CIXING+ "\" and " + COLNAME_DICTINDEX + "=\"" + dictid
                    + "\" and " + COLNAME_CIXINGINDEX + "=\"" + cixing_id   + "\"";
            ssel += " and " + COLUMN_ID + " >= " + ihead + " and " + COLUMN_ID + "<=" + itail;
            c = m_dbInternal.query(TBNAME_LOCALDICTCACHE,
                    new String[]{COLNAME_TYPEVALUE},
                    ssel,
                    null, null, null, COLUMN_ID);
            if(null != c && c.moveToFirst()) {
                return c.getString(0);
            }
        }
        catch(Exception e){
            TLog.log("DictionaryCNDB : getwordtextitemrange : e : " + e);
        }
        finally{
            if(null != c) {
                c.close();
            }
        }
        return null;
    }

    public String getWordDictCixingFushu(int dictid, int cixing_id, int bookid, String sentence, int wordindex, int ihead, int itail){
        if(null == m_dbInternal || TextUtils.isEmpty(sentence)){
            return null;
        }
        Cursor c = null;
        try{
            String ssel = COLUMN_BOOKID + "=\"" + bookid + "\" and " + COLUMN_SENTENCE + "=\"" + makedbreadablestring(sentence) + "\" and " +
                    COLUMN_WORDINDEX + "=\"" + wordindex + "\" and " + COLNAME_TYPE + "=\"" + TYPENAME_FUSHU+ "\" and " + COLNAME_DICTINDEX + "=\"" + dictid
                    + "\" and " + COLNAME_CIXINGINDEX + "=\"" + cixing_id + "\""  ;
            ssel += " and " + COLUMN_ID + " >= " + ihead + " and " + COLUMN_ID + "<=" + itail;
            c = m_dbInternal.query(TBNAME_LOCALDICTCACHE,
                    new String[]{COLNAME_TYPEVALUE},
                    ssel,
                    null, null, null, COLUMN_ID);
            if(null != c && c.moveToFirst()) {
                return c.getString(0);
            }
        }
        catch(Exception e){
            TLog.log("DictionaryCNDB : getwordtextitemrange : e : " + e);
        }
        finally{
            if(null != c) {
                c.close();
            }
        }
        return null;
    }

    public IntDouble[] getWordDictCiXingJieShiRanges(int dictid, int cixing_id, int bookid, String sentence, int wordindex, int ihead, int itail){
        if(null == m_dbInternal || TextUtils.isEmpty(sentence) || wordindex < 0){
            return null;
        }
        Cursor c = null;
        try{
            String ssel = COLUMN_BOOKID + "=\"" + bookid + "\" and " + COLUMN_SENTENCE + "=\"" + makedbreadablestring(sentence) + "\" and " +
                    COLUMN_WORDINDEX + "=\"" + wordindex + "\" and " + COLNAME_TYPE + "=\"" + TYPENAME_JIESHI+ "\" and " + COLNAME_DICTINDEX + "=\"" + dictid
                    + "\" and " + COLNAME_CIXINGINDEX + "=\"" + cixing_id  + "\"" ;
            ssel += " and " + COLUMN_ID + " >= " + ihead + " and " + COLUMN_ID + "<=" + itail;
            c = m_dbInternal.query(TBNAME_LOCALDICTCACHE,
                    new String[]{COLUMN_ID},
                    ssel,
                    null, null, null, COLUMN_ID);
            if(null != c && c.moveToFirst()) {
                IntDouble idus[] = new IntDouble[c.getCount()];
                int idx = 0;
                idus[idx] = new IntDouble();
                idus[idx++].m_ival1 = c.getInt(0);
                while(c.moveToNext()) {
                    idus[idx] = new IntDouble();
                    idus[idx++].m_ival1 = c.getInt(0);
                }
                if(null != c) {
                    c.close();
                }
                c = null;
                for(int i = 0; i < idus.length - 1; i++) {
                    idus[i].m_ival2 = idus[i + 1].m_ival1 - 1;
                }
                idus[idus.length - 1].m_ival2 = itail;
                return idus;
            }
        }
        catch(Exception e){
            TLog.log("DictionaryCNDB : getwordtextitemrange : e : " + e);
        }
        finally{
            if(null != c) {
                c.close();
            }
        }
        return null;
    }




    public IntDouble[] getWorDictCixingRanges(int dictid, int bookid, String sentence, int wordindex, int ihead, int itail){
        if(null == m_dbInternal || TextUtils.isEmpty(sentence) || wordindex < 0){
            return null;
        }
        Cursor c = null;
        try{
            String ssel = COLUMN_BOOKID + "=\"" + bookid + "\" and " + COLUMN_SENTENCE + "=\"" + makedbreadablestring(sentence) + "\" and " +
                    COLUMN_WORDINDEX + "=\"" + wordindex + "\" and " + COLNAME_DICTINDEX + "=\"" + dictid + "\"";
            ssel += " and " + COLUMN_ID + " >= " + ihead + " and " + COLUMN_ID + "<=" + itail;
            c = m_dbInternal.query(TBNAME_LOCALDICTCACHE,
                    new String[]{COLUMN_ID, COLNAME_CIXINGINDEX},
                    ssel,
                    null, null, null, COLUMN_ID);
            if(null != c && c.moveToFirst()) {
                ArrayList<IntDouble> iduslist = new ArrayList<IntDouble>();
                int curcixingid = c.getInt(1);
                IntDouble  item = null;
                if (curcixingid >= 0) {
                    item = new IntDouble();
                    item.m_ival1 = c.getInt(0);
                    item.m_ival2 = c.getInt(0);
                    iduslist.add(item);
                }

                while(c.moveToNext()) {
                    int newcixingid = c.getInt(1);
                    if (newcixingid != curcixingid && newcixingid >= 0) {
                        item = new IntDouble();
                        item.m_ival1 = c.getInt(0);
                        item.m_ival2 = c.getInt(0);
                        iduslist.add(item);
                    } else if (item != null) {
                        item.m_ival2 = c.getInt(0);
                    }

                    curcixingid = newcixingid;

                }
                if(null != c) {
                    c.close();
                }
                c = null;
                if (iduslist != null) {
                    int size = iduslist.size();
                    if (size > 0) {
                        IntDouble[] idus = new IntDouble[size];
                        for (int i = 0; i < size; i++) {
                            idus[i] = iduslist.get(i);
                        }
                        iduslist.clear();

                        return idus;
                    }
                }
            }
        }
        catch(Exception e){
           TLog.log("DictionaryCNDB : getwordtextitemrange : e : " + e);
        }
        finally{
            if(null != c) {
                c.close();
            }
        }
        return null;
    }






    public void insertDictionaryCache(EnWordDictDescData wdd) {
        if(null == m_dbInternal || null == wdd) {
            return;
        }
        if(null == wdd.mDicts || wdd.mDicts.length <= 0) {
            return;
        }

        Cursor c = null;
        try{
            c = m_dbInternal.query(TBNAME_LOCALDICTCACHE,
                    new String[]{COLNAME_TYPEVALUE},
                    COLUMN_SENTENCE + "=\"" + makedbreadablestring(wdd.mPhrases) +  "\" and " + COLUMN_BOOKID + "=\"" + wdd.m_ibookid
                    + "\" and " + COLUMN_WORDINDEX + "=\"" + wdd.mPhraseIndex + "\" and " + COLNAME_TYPE + " =\"" + TYPENAME_BASEWORD + "\"" ,
                    null, null, null, COLUMN_ID);
            if(null != c && c.moveToFirst()) {
                String baseword = c.getString(0);
                if(!TextUtils.isEmpty(baseword) && baseword.contentEquals(wdd.mBaseWord)){
                    return;
                }
            }
        } catch(Exception e0) {
            TLog.log("DictionaryCNDB : getwordtextitemrange : e0 : " + e0);
        } finally{
            if(null != c) {
                c.close();
            }
        }

        ArrayList<String> sqls = new ArrayList<String>();
        String sql = "";

        for(int i = 0; i < wdd.mDicts.length; i++) {
            if (null == wdd.mDicts[i]) {
                continue;
            }

            sql = String.format(INSERT_SQL_LOCALDICTCACHE,
                    wdd.m_inetid, wdd.m_ibookid, makedbreadablestring(wdd.mPhrases), wdd.mPhraseIndex, wdd.mClickWord, i, -1,
                    TYPENAME_BASEWORD, wdd.mBaseWord);
            sqls.add(sql);

            if(null != wdd.m_ssduyin && wdd.m_ssduyin.length > 0) {
                StringBuilder sb = new StringBuilder();
                for(int m = 0; m < wdd.m_ssduyin.length; m++) {
                    if(!TextUtils.isEmpty(wdd.m_ssduyin[m])){
                        sb.append(wdd.m_ssduyin[m]);
                        sb.append(";");
                    }
                }
                sql = String.format(INSERT_SQL_LOCALDICTCACHE, +
                                wdd.m_inetid, wdd.m_ibookid, makedbreadablestring(wdd.mPhrases), wdd.mPhraseIndex, wdd.mClickWord, i, -1,
                        TYPENAME_SOUNDURL, sb.toString());
                sqls.add(sql);
                sb = null;
            }

            sql = String.format(INSERT_SQL_LOCALDICTCACHE,
                    wdd.m_inetid, wdd.m_ibookid, makedbreadablestring(wdd.mPhrases), wdd.mPhraseIndex, wdd.mClickWord, i, -1,
                    TYPENAME_DICTNAME, wdd.mDicts[i].mDictName);
            sqls.add(sql);
            sql = String.format(INSERT_SQL_LOCALDICTCACHE,
                    wdd.m_inetid, wdd.m_ibookid, makedbreadablestring(wdd.mPhrases), wdd.mPhraseIndex, wdd.mClickWord, i, -1,
                    TYPENAME_DICTTYPE, wdd.mDicts[i].mDictType);
            sqls.add(sql);

            sql = String.format(INSERT_SQL_LOCALDICTCACHE,
                    wdd.m_inetid, wdd.m_ibookid, makedbreadablestring(wdd.mPhrases), wdd.mPhraseIndex, wdd.mClickWord, i, -1,
                    TYPENAME_YINBIAO, makedbreadablestring(wdd.mDicts[i].mYinBiao));
            sqls.add(sql);


            if (null != wdd.mDicts[i].mCiXings && wdd.mDicts[i].mCiXings.length > 0) {
                for (int j = 0; j < wdd.mDicts[i].mCiXings.length; j++) {

                    sql = String.format(INSERT_SQL_LOCALDICTCACHE,
                            wdd.m_inetid, wdd.m_ibookid, makedbreadablestring(wdd.mPhrases), wdd.mPhraseIndex, wdd.mClickWord, i, j,
                            TYPENAME_CIXING, wdd.mDicts[i].mCiXings[j].mCXdecs);
                    sqls.add(sql);

                    sql = String.format(INSERT_SQL_LOCALDICTCACHE,
                            wdd.m_inetid, wdd.m_ibookid, makedbreadablestring(wdd.mPhrases), wdd.mPhraseIndex, wdd.mClickWord, i, j,
                            TYPENAME_FUSHU, wdd.mDicts[i].mCiXings[j].mFuShu);
                    sqls.add(sql);


                    if (null != wdd.mDicts[i].mCiXings[j].mJieShis && wdd.mDicts[i].mCiXings[j].mJieShis.length > 0) {
                        for (int k = 0; k < wdd.mDicts[i].mCiXings[j].mJieShis.length; k++) {

                            sql = String.format(INSERT_SQL_LOCALDICTCACHE,
                                    wdd.m_inetid, wdd.m_ibookid, makedbreadablestring(wdd.mPhrases), wdd.mPhraseIndex, wdd.mClickWord, i, j,
                                    TYPENAME_JIESHI, makedbreadablestring(wdd.mDicts[i].mCiXings[j].mJieShis[k].mShiYi));
                            sqls.add(sql);

                            if (null != wdd.mDicts[i].mCiXings[j].mJieShis[k].mLiJus && wdd.mDicts[i].mCiXings[j].mJieShis[k].mLiJus.length > 0) {
                                for (int m = 0; m < wdd.mDicts[i].mCiXings[j].mJieShis[k].mLiJus.length; m++) {
                                    if (!TextUtils.isEmpty(wdd.mDicts[i].mCiXings[j].mJieShis[k].mLiJus[m].mLiJu)) {
                                        sql = String.format(INSERT_SQL_LOCALDICTCACHE,
                                                wdd.m_inetid, wdd.m_ibookid, makedbreadablestring(wdd.mPhrases), wdd.mPhraseIndex, wdd.mClickWord, i, j,
                                                TYPENAME_LIJU, makedbreadablestring(wdd.mDicts[i].mCiXings[j].mJieShis[k].mLiJus[m].mLiJu));
                                        sqls.add(sql);
                                    }
                                    if (!TextUtils.isEmpty(wdd.mDicts[i].mCiXings[j].mJieShis[k].mLiJus[m].mLiJuJieShi)) {
                                        sql = String.format(INSERT_SQL_LOCALDICTCACHE,
                                                wdd.m_inetid, wdd.m_ibookid, makedbreadablestring(wdd.mPhrases), wdd.mPhraseIndex, wdd.mClickWord, i, j,
                                                TYPENAME_LIJUJIESHI, makedbreadablestring(wdd.mDicts[i].mCiXings[j].mJieShis[k].mLiJus[m].mLiJuJieShi));
                                        sqls.add(sql);
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        dbexcute(sqls);
    }

    public String getWordDictCiXingJieShiByDataBaseId(int idatabaseid){
        if(null == m_dbInternal){
            return null;
        }
        Cursor c = null;
        try{
            c = m_dbInternal.query(TBNAME_LOCALDICTCACHE,
                    new String[]{COLNAME_TYPEVALUE},
                    COLUMN_ID + "=\"" + idatabaseid + "\"",
                    null, null, null, null);
            if(null != c && c.moveToFirst()) {
                return makeUIreadablestring(c.getString(0));
            }
        }
        catch(Exception e){
            TLog.log("DictionaryCNDB : getwordshiyibydatabaseid : e : " + e);
        }
        finally{
            if(null != c) {
                c.close();
            }
        }
        return null;
    }

    public boolean getWordDictCiXingJieShiLiJus(int idatabasefrom, int idatabaseto, ArrayList<EnWordDictDescData.EnParcelableCizu> aloutliju){
        if(idatabasefrom > idatabaseto ||  null == aloutliju) {
            return false;
        }

        ArrayList<EnWordDictDescData.EnParcelableCizu> alpliju = aloutliju;
        alpliju.clear();
        for(int i = idatabasefrom; i <= idatabaseto; i++) {
            Cursor c = null;
            try{
                c = m_dbInternal.query(TBNAME_LOCALDICTCACHE,
                        new String[]{COLNAME_TYPE, COLNAME_TYPEVALUE},
                        COLUMN_ID + "=\"" + i + "\"",
                        null, null, null, null);
                if(null != c && c.moveToFirst()) {
                    String stype = c.getString(0);
                    if(stype.contentEquals(TYPENAME_LIJU)){
                        EnWordDictDescData.EnParcelableCizu pc = new EnWordDictDescData.EnParcelableCizu();
                        alpliju.add(pc);
                        pc.mLiJu = makeUIreadablestring(c.getString(1));
                    }
                    else if(stype.contentEquals(TYPENAME_LIJUJIESHI)){
                        int iupper = alpliju.size() - 1;
                        if(iupper >= 0) {
                            alpliju.get(iupper).mLiJuJieShi = makeUIreadablestring(c.getString(1));
                        }
                    }
                }
            }
            catch(Exception e){
                TLog.log("DictionaryENDB : getwordspecificcizusandliju : e : " + e);
            }
            finally{
                if(null != c) {
                    c.close();
                }
            }
        }
        return true;
    }


    private void dbexcute(ArrayList<String> sqls) {
        if(null == m_dbInternal || null == sqls || sqls.size() <= 0) {
            return;
        }
        try {
            m_dbInternal.beginTransaction();
            for(String sql : sqls) {
                if(null != sql) {
                    try {
                        m_dbInternal.execSQL(sql); }
                    catch(Exception e){
                    }
                }
            }
            m_dbInternal.setTransactionSuccessful();
        } finally {
            m_dbInternal.endTransaction();
        }
    }

    private String makedbreadablestring(String s) {
        if(TextUtils.isEmpty(s)) {
            return s;
        }
        return s.replace("\"", "&quot;");
    }

    private String makeUIreadablestring(String s) {
        if(TextUtils.isEmpty(s)) {
            return s;
        }
        return s.replace("&quot;", "\"");
    }

    private static class DatabaseHelper extends SQLiteOpenHelper {
        public DatabaseHelper(Context context, String sFilepath) {
            super(context, sFilepath, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db)  {
            db.execSQL(CREATE_SQL_LOCALDICTCACHE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            final String strDrop = "DROP TABLE IF EXISTS ";
            db.execSQL(strDrop + CREATE_SQL_LOCALDICTCACHE);
            onCreate(db);
        }
    }

}
